package org.linlinjava.litemall.db.service;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.linlinjava.litemall.db.dao.IdentityCardMapper;
import org.linlinjava.litemall.db.domain.IdentityCard;
import org.linlinjava.litemall.db.domain.IdentityCardExample;
import org.linlinjava.litemall.db.service.IdentityCardService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.github.pagehelper.PageHelper;

import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
 
/**
 * @Author: syl
 * @Date: 2019/7/3 0003 16:39
 * @Description:
 */
 
@Service
public class IdentityCardService {
	
    @Autowired
    private IdentityCardMapper identityCardMapper;
    
    
    public int insert(IdentityCard ic) {
    	return identityCardMapper.insert(ic);
    }
    
    
    public List<IdentityCard> queryByPid(Integer pid,Integer page, Integer limit){
    	IdentityCardExample example = new IdentityCardExample();
    	example.or().andPidEqualTo(pid);
    	PageHelper.startPage(page, limit);
    	return identityCardMapper.selectByExample(example);
    }
    
    public List<IdentityCard> selectMember(Integer pid){
    	IdentityCardExample example = new IdentityCardExample();
    	example.or().andPidEqualTo(pid);
    	return identityCardMapper.selectByExample(example);
    }
    
	 /*
    public static void main(String[] args) {
        getAllByExcel("F:\\b.xlsx");
 
    }
    
    public static void api() {
    	getAllByExcel("F:\\b.xlsx");
    }
    
   
    public static void getAllByExcel(String filepath) {
        try {
            // 同时支持Excel 2003、2007
            File excelFile = new File(filepath); // 创建文件对象
            FileInputStream is = new FileInputStream(excelFile); // 文件流
            Workbook workbook = WorkbookFactory.create(is); // 这种方式 Excel
 
            String[] res = readExcel(workbook, 0, 1, 0);
            for (int i = 0; i < res.length; i++) {
                System.out.println(res[i]);
            }
 
        } catch (Exception e) {
            e.printStackTrace();
        }
 
    }*/
    public void readExcel2(Workbook wb, int sheetIndex, int startReadLine, int tailLine) {
        //存储页的list
		List sheetList= new ArrayList();
		// 获得工作簿
		Workbook workbook = wb;
		//本地文件
		//File file=new File("");
		// Workbook workbook = WorkbookFactory.create(file);
		// 获得工作表个数
		int sheetCount = workbook.getNumberOfSheets();
		// 遍历工作表
		for (int i = 0; i < sheetCount; i++) {
		    //储存行的List
		    List rowList=new ArrayList();
		    Sheet sheet = workbook.getSheetAt(i);
		    // 获得行数
		    int rows = sheet.getLastRowNum() + 1;
		    // 获得列数，先获得一行，在得到该行列数
		    Row tmp = sheet.getRow(i);
		    if (tmp == null) {
		        sheetList.add(i,null);
		        continue;
		    }
		    int cols = tmp.getPhysicalNumberOfCells();
		    // 读取数据
		    for (int row = 0; row < rows; row++) {
		        //储存列的数据
		        List coluList=new ArrayList();
		        Row r = sheet.getRow(row);
		        for (int col = 0; col < cols; col++) {
		            if(r!=null){
		                if(r.getCell(col)!=null){
		                    Object  value=null;
		                    if(r.getCell(col).getCellType() == HSSFCell.CELL_TYPE_NUMERIC && HSSFDateUtil.isCellDateFormatted(r.getCell(col))){
		                        Date date = r.getCell(col).getDateCellValue();
		                        //装换为时间格式
		                        String formatDate = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(date);
		                        value=formatDate;
		                    }else {
		                        r.getCell(col).setCellType(CellType.STRING);
		                        value=r.getCell(col).getStringCellValue();
		                    }
		                    coluList.add(col,value);
		                }else {
		                    coluList.add(col,null);
		                }
		            }else {
		                coluList.add(col,null);
		            }
		        }
		        rowList.add(row,coluList);
		    }
		    sheetList.add(i,rowList);
		}
		System.out.println(sheetList);
    }
    
    public void readExcel(Workbook wb, int sheetIndex, int startReadLine, int tailLine) {
    	

        //String[] res = readExcel(workbook, 0, 1, 0);
        
        Sheet sheet = wb.getSheetAt(sheetIndex);
        Row row = null;
        String[] res = new String[sheet.getLastRowNum() - tailLine + 1];
        
        //IdentityCardService identityCardService = BeanUtil.getBean(IdentityCardService.class);
        int pid = 1;
        String str = "";
        Integer IcID = 0;
        for (int i = startReadLine; i < sheet.getLastRowNum() - tailLine + 1; i++) {
            row = sheet.getRow(i);
            res[i] = "";
            int index = 0;
            
            
            IdentityCard ic = new IdentityCard();
            for (Cell c : row) {
            	int num = row.getLastCellNum();
            	System.out.println("一行有多少列："+num);
                boolean isMerge = isMergedRegion(sheet, i, c.getColumnIndex());
                // 判断是否具有合并单元格
                if (isMerge) {
                    String rs = getMergedRegionValue(sheet, row.getRowNum(), c.getColumnIndex());
                    //res[i] += rs+ "_";
                    IdentityCard Ic = new IdentityCard();
                    Ic.setName(rs);
                    Ic.setPid(0);
                    if(!str.equals(rs)) {
                    	identityCardMapper.insert(Ic);
                    	//pid = i+1;
                    	str = rs;
                    	IcID = Ic.getId();
                    }
                } else {
                	IdentityCard Ic = new IdentityCard();
                    res[i] += getCellValue(c)+ "_";
                    if(index == 0) {
                    	ic.setName(getCellValue(c));
                    	ic.setPid(IcID);
                    }else if(index == 1) {
                    	ic.setRelation(getCellValue(c));
                    }else if(index == 2) {
                    	if(getCellValue(c).equals("户主")) {
                    		Ic.setName(ic.getName());
                    		Ic.setPid(0);
                    		identityCardMapper.insert(Ic);
                    		ic.setPid(Ic.getId());
                    		ic.setRelation("户主");
                    		index--;
                    	}
                    	ic.setSex(getCellValue(c));
                    }else if(index == 3) {
                    	ic.setBirthday(getCellValue(c));
                    }else if(index == 4) {
                    	ic.setCard(getCellValue(c));
                    }
                    index++;
                }
            }
            identityCardMapper.insert(ic);
            
        }
        /*
        if(startReadLine > 0){
            String[] result = new String[res.length - startReadLine];
            for (int i = 0; i < startReadLine; i++) {
                for (int j = 0; j < res.length; j++) {
                    if(j == res.length - 1)
                        continue;
                    res[j] = res[j+1];
                }
            }
            for (int i = 0; i < result.length; i++) {
                result[i] = res[i];
            }
            //return result;
        }else{
            //return res;
        }*/
    }
 
    private static boolean isMergedRegion(Sheet sheet, int row, int column) {
        int sheetMergeCount = sheet.getNumMergedRegions();
        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress range = sheet.getMergedRegion(i);
            int firstColumn = range.getFirstColumn();
            int lastColumn = range.getLastColumn();
            int firstRow = range.getFirstRow();
            int lastRow = range.getLastRow();
            if (row >= firstRow && row <= lastRow) {
                if (column >= firstColumn && column <= lastColumn) {
                    return true;
                }
            }
        }
        return false;
    }
 
    public static String getMergedRegionValue(Sheet sheet, int row, int column) {
        int sheetMergeCount = sheet.getNumMergedRegions();
 
        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress ca = sheet.getMergedRegion(i);
            int firstColumn = ca.getFirstColumn();
            int lastColumn = ca.getLastColumn();
            int firstRow = ca.getFirstRow();
            int lastRow = ca.getLastRow();
 
            if (row >= firstRow && row <= lastRow) {
 
                if (column >= firstColumn && column <= lastColumn) {
                    Row fRow = sheet.getRow(firstRow);
                    Cell fCell = fRow.getCell(firstColumn);
                    return getCellValue(fCell);
                }
            }
        }
 
        return null;
    }
 
    private static String getCellValue(Cell cell) {
        SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd");
        String cellValue = "";
        int cellType = cell.getCellType();
        switch (cellType) {
            case Cell.CELL_TYPE_STRING: // 文本
                cellValue = cell.getStringCellValue();
                break;
            case Cell.CELL_TYPE_NUMERIC: // 数字、日期
                if (DateUtil.isCellDateFormatted(cell)) {
                    cellValue = fmt.format(cell.getDateCellValue()); // 日期型
                } else {
                    cellValue = String.valueOf((int) cell.getNumericCellValue()); // 数字
                }
                break;
            case Cell.CELL_TYPE_BOOLEAN: // 布尔型
                cellValue = String.valueOf(cell.getBooleanCellValue());
                break;
            case Cell.CELL_TYPE_BLANK: // 空白
                cellValue = cell.getStringCellValue();
                break;
            case Cell.CELL_TYPE_ERROR: // 错误
                cellValue = "错误";
                break;
            case Cell.CELL_TYPE_FORMULA: // 公式
                cellValue = "错误";
                break;
            default:
                cellValue = "错误";
        }
        return cellValue;
    }
}